package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Teaching_schedule;

public class TeachingScheduleMod {
	Connection connection;
	public static TeachingScheduleMod isntanceTSM;
	public static TeachingScheduleMod getIsntance(){
		if(isntanceTSM == null){
			isntanceTSM = new TeachingScheduleMod();
		}
		return isntanceTSM;
	}
	public boolean add(Teaching_schedule ts){
		boolean result = false;
		PreparedStatement sqlu =null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql ="INSERT INTO teaching_schedule(date, time, tid, clid, sjid) VALUES (? , ? , ? , ? , ?) LIMIT = 1 ";
			sqlu = connection.prepareStatement(sql);
			sqlu.setDate(1, ts.getDate());
			sqlu.setTime(2, ts.getTime());
			sqlu.setInt(3, ts.getTid());
			sqlu.setInt(4,ts.getClid());
			sqlu.setInt(5, ts.getSjid());
			int n = sqlu.executeUpdate();
			if(n > 0) result = true;
			sqlu.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean Update(Teaching_schedule ts , int id){
		boolean result = false;
		PreparedStatement sqlu =null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql ="UPDATE teaching_schedule SET date = ?, time = ?, tid = ? , clid = ?, sjid = ? WHERE tcsid = ? LIMIT = 1 ";
			sqlu = connection.prepareStatement(sql);
			sqlu.setDate(1, ts.getDate());
			sqlu.setTime(2, ts.getTime());
			sqlu.setInt(3, ts.getTid());
			sqlu.setInt(4,ts.getClid());
			sqlu.setInt(5, ts.getSjid());
			sqlu.setInt(6, id);
			int n = sqlu.executeUpdate();
			if(n > 0) result = true;
			sqlu.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public ArrayList<Teaching_schedule> getByTid(int tid){
		ArrayList<Teaching_schedule> result = null;
		PreparedStatement sqls = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql = "SELECT * FROM teaching_schedule WHERE tid = ? ";
			sqls.setInt(1, tid);
			sqls= connection.prepareStatement(sql);
			ResultSet rs = sqls.executeQuery();
			while(rs.next()){
				result.add(new Teaching_schedule(rs.getInt(1), rs.getDate(2), rs.getTime(3), rs.getInt(4), rs.getInt(5), rs.getInt(6)));
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public ArrayList<Teaching_schedule> getByClid(int clid){
		ArrayList<Teaching_schedule> result = null;
		PreparedStatement sqls = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql = "SELECT * FROM teaching_schedule WHERE clid = ? ";
			sqls.setInt(1, clid);
			sqls= connection.prepareStatement(sql);
			ResultSet rs = sqls.executeQuery();
			while(rs.next()){
				result.add(new Teaching_schedule(rs.getInt(1), rs.getDate(2), rs.getTime(3), rs.getInt(4), rs.getInt(5), rs.getInt(6)));
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	 public boolean del(int tcid){
			PreparedStatement delpd = null;
			boolean result = false;
			Connection connection = DB_Connection.getIsntance().getConnection();
			try {
				
				delpd = connection.prepareStatement("DELETE FROM teaching_schedule WHERE tcid = ? LIMIT 1");
				delpd.setInt(1, tcid);
				int number = delpd.executeUpdate();
				if (number > 0) {
					result = true;
				}
				delpd.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return  result;
		}
}
